﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Diagnostics;
using System.ServiceModel;
using System.Runtime.Serialization;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;


namespace DragDraw.Service
{
    public class UserInfoData:SqlDataManager
    {
        ///<summary>
        ///插入UserInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public bool Insert(UserInfo userInfo)//what if the id exist?
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "InsertUserInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@userID", SqlDbType.NVarChar, 20);
            curCommand.Parameters.Add("@realname", SqlDbType.NVarChar, 20);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@userID"].Value = userInfo.UserID;
            curCommand.Parameters["@realname"].Value = userInfo.Realname;

            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("UserInfoData.Insert:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataInsertException("UserInfoData.Insert:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("UserInfoData.Insert:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return true;
        }
        ///<summary>
        ///删除UserInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public bool Delete(string userID)
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "DeleteUserInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@userID", SqlDbType.NVarChar, 20);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@userID"].Value = userID;
            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("UserInfoData.Delete:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataErrorException("UserInfoData.Delete:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("UserInfoData.Delete:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return true;


        }
        ///<summary>
        ///更新UserInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public bool Update(UserInfo userInfo)
        {
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.StoredProcedure;
            curCommand.CommandText = "UpdateUserInfo";
            curCommand.Parameters.Add("@result", SqlDbType.Int);
            curCommand.Parameters.Add("@userID", SqlDbType.NVarChar, 20);
            curCommand.Parameters.Add("@realname", SqlDbType.NVarChar, 20);
            curCommand.Parameters["@result"].Direction = ParameterDirection.Output;
            curCommand.Parameters["@userID"].Value = userInfo.UserID;
            curCommand.Parameters["@realname"].Value = userInfo.Realname;

            curCommand.Connection = SqlDataManager.OpenConnection();
            int result = 0;
            try
            {
                result = curCommand.ExecuteNonQuery();
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("UserInfoData.Update:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (Convert.ToInt32(curCommand.Parameters["@result"].Value) == 0)//有没有执行sql语句？
            {
                throw new DataErrorException("UserInfoData.Update:没有执行sql语句！", null);
            }
            if (result <= 0)//执行的sql语句是否成功？
            {
                throw new DataErrorException("UserInfoData.Update:执行了sql语句，可是sql语句没有影响任何一条记录！", null);
            }
            return true;
        }
        ///<summary>
        ///查询UserInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public UserInfo Query(string userID)//if does not exist,return null
        {
            string result_userID="", result_realname="";
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from UserInfo where UserID='" + userID + "'";

            curCommand.Connection = SqlDataManager.OpenConnection();
            bool HasResult = false;
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.HasRows)
                {
                    HasResult = true;
                    while (reader.Read())
                    {
                        result_userID = reader.GetString(0);
                        result_realname = reader.GetString(1);
                    }
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("UserInfoData.Query:执行SqlCommand的时候发生错误！\n" + 
                    error.Message, error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
            }
            if (!HasResult)
            {
                throw new DataNotFoundException("UserInfoData.Query:顺利执行完sql语句，可是没有找到匹配的记录！", null);
            }
            UserInfo result_userInfo = new UserInfo(result_userID, result_realname);
            return result_userInfo;
        }
        ///<summary>
        ///显示所有UserInfo信息
        ///</summary>
        ///<exception cref="DataErrorException">数据库执行错误</exception>
        ///<exception cref="DataNotFoundException">查找不到相应记录</exception>
        static public void Show()
        {
            Console.WriteLine("-----UserInfo Table------------------");
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from UserInfo";
            curCommand.Connection = SqlDataManager.OpenConnection();
            string output;
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.Default);
                while (reader.Read())
                {
                    for (int i = 0; i < reader.VisibleFieldCount; i++)
                    {
                        output = reader.GetString(i);
                        Console.Write("{0} ", output);
                    }
                    Console.WriteLine();
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                throw new DataErrorException("UserInfoData.Show:执行SqlCommand的时候发生错误！", error);
            }
            finally
            {
                SqlDataManager.CloseConnection(curCommand.Connection);
                Console.WriteLine("-------------------------------------");
            }
        }
        // *******************国笙加上去的
        static public List<string> GetAllUserInfo()
        {
            List<string> userID = new List<string>();
            Console.WriteLine("-----UserInfo Table------------------");
            SqlCommand curCommand;
            curCommand = new SqlCommand();
            curCommand.CommandType = CommandType.Text;
            curCommand.CommandText = "select * from UserInfo";
            curCommand.Connection = SqlDataManager.OpenConnection();
            try
            {
                SqlDataReader reader = curCommand.ExecuteReader(CommandBehavior.Default);
                while (reader.Read())
                {
                    userID.Add(reader.GetString(0));
                }
            }
            catch (Exception error)
            {
                Console.WriteLine(error.Message);
                SqlDataManager.CloseConnection(curCommand.Connection);
                return null;
            }
            SqlDataManager.CloseConnection(curCommand.Connection);
            return userID;
        }
    }
}